﻿#region references
using System;
using System.Data;
using System.Data.SqlClient;
using System.Collections.Generic;

/* Project dependencies */
using IcaGames.Logging;
using IcaGames.Objects;
using IcaGames.Database.ObjectManagement;
#endregion

namespace IcaGames.Database
{
    /*
     * Class that holds common methods used to execute Sql into the database.
     * 
     * @author
     * Rick Oonk
     * 
     * @version
     * 1.0
     * 
     * @update
     * 20-11-2009 by Rick Oonk.
     * Created Class and added attributes and standard functions. 
     */
    public static class DatabaseStore
    {
        /*
         * Function to execute a Stored Procedure from the database.
         * 
         * @param
         * spName: The name of the Stored Procedure.
         * spParameters: The parameters of the Stored Procedure.
         * spType: The type of Stored Procedure - Insert, Update or Delete.
         * 
         * @returns
         * True if the Stored Procedure was executed correctly.
         * False if the Stored Procedure was not executed correctly.
         */
        public static bool ExecuteStoredProcedure(string spName, string[,] spParameters, SpType spType)
        {
            SqlCommand command = null;
            try
            {
                command = Connection.GetCommand(spName);
                command.CommandType = CommandType.StoredProcedure;

                /* Add the parameters from the string array */
                if (spParameters.Length > 0)
                {
                    for (int i = 0; i < spParameters.Length; i++)
                    {
                        command.Parameters.AddWithValue(spParameters[i, 0], spParameters[i, 1]);
                    }
                    if (spType == SpType.Insert) { command.ExecuteNonQuery(); }
                    else { command.ExecuteReader(); }
                }
                else
                {
                    /* If there are no parameters specified, then return false */
                    return false;
                }
            }
            catch (Exception ex)
            {
                Log.CreateLog("Exception while executing " + spType + " Stored Procedure: " + spName, "DATABASESTORE");
                Log.CreateLog(ex.Message, "DATABASESTORE");
                return false;
            }
            finally
            {
                Connection.CloseCommand(command);
            }

            return true;
        }

        public static List<Game> GetSearchResultsByPlatform(string platform)
        {
            List<Game> games = new List<Game>();
            SqlCommand command = null;
            SqlDataReader dr = null;
            string commandText = "spGetGamesByPlatform";

            try
            {
                int platformId = PlatformValue.GetPlatformValue(platform);

                command = Connection.GetCommand(commandText);
                command.CommandType = CommandType.StoredProcedure;
                command.Parameters.AddWithValue("@platformId", platformId);

                dr = Connection.GetSqlDataReader(command);
                while (dr.Read())
                {
                    Game game = new Game
                    {
                        GameNr = Cast.CastToInteger(dr["GAME_NR"]),
                        DescriptionShort = Cast.CastToString(dr["BESCHRIJVING_KORT"]),
                        Price = Cast.CastToDecimal(dr["PRIJS"]),
                        Title = Cast.CastToString(dr["TITEL"]),
                        Stock = Cast.CastToInteger(dr["VOORRAAD"]),
                        Platform = (Platform)Cast.CastToInteger(dr["PLATFORM_ID"])
                    };
                    games.Add(game);
                }
            }
            catch (Exception ex)
            {
                Log.CreateLog("Exception while getting Games by Platform", "DATABASESTORE");
                Log.CreateLog(ex.Message, "DATABASESTORE");
            }
            finally
            {
                Connection.CloseCommand(command, dr);
            }
            return games;
        }

        /*
         * Function to get a List containing Game search results for a specified searchQuery.
         * 
         * @param
         * searchQuery: The query that has to be executed for the search.
         * 
         * @returns
         * A List containing the Game search results.
         */
        public static List<Game> GetSearchResults(string searchQuery)
        {
            List<Game> games = new List<Game>();
            SqlCommand command = null;
            SqlDataReader dr = null;
            string commandText = "spGetSearchResults";
            string[] finalSearch = searchQuery.Split(' ');
            bool gameExists = false;

            try
            {
                command = Connection.GetCommand(commandText);
                command.CommandType = CommandType.StoredProcedure;

                foreach (string query in finalSearch)
                {
                    /* Clear the parameter list and add @query parameter to the SqlCommand */
                    command.Parameters.Clear();
                    command.Parameters.AddWithValue("@query", query);
                    gameExists = false;

                    dr = Connection.GetSqlDataReader(command);
                    while (dr.Read())
                    {
                        Game game = new Game
                        {
                            GameNr = Cast.CastToInteger(dr["GAME_NR"]),
                            DescriptionShort = Cast.CastToString(dr["BESCHRIJVING_KORT"]),
                            Price = Cast.CastToDecimal(dr["PRIJS"]),
                            Title = Cast.CastToString(dr["TITEL"]),
                            Stock = Cast.CastToInteger(dr["VOORRAAD"]),
                            Platform = (Platform)Cast.CastToInteger(dr["PLATFORM_ID"])
                        };

                        /* Check if the game allready exists in the list before adding it to the list */
                        foreach (Game g in games) 
                        {
                            if (g.GameNr == game.GameNr && g.Platform == game.Platform) { gameExists = true; break; }
                        }
                        if (!gameExists) { games.Add(game); }
                    }

                    /* Check if the datareader is still opened and close it if the reader is still opened */
                    if (dr != null) { dr.Close(); }
                }
            }
            catch (Exception ex)
            {
                Log.CreateLog("Exception while getting Game search results", "DATABASESTORE");
                Log.CreateLog(ex.Message, "DATABASESTORE");
            }
            finally
            {
                Connection.CloseCommand(command);
            }

            return games;
        }

        /*
         * Function to get a List containing Game search results for a specified searchQuery.
         * 
         * @param
         * gameTitle: The title of the Game.
         * gameSize: The size of the Game.
         * gamePlatform: The platform of the Game.
         * 
         * @returns
         * A List containing the Game search results.
         */
        public static List<Game> GetSearchResults(string gameTitle, 
            string gameSize, string gamePlatform)
        {
            List<Game> games = new List<Game>();
            SqlCommand command = null;
            SqlDataReader dr = null;
            string commandText = string.Empty;

            try
            {
                command = Connection.GetCommand(commandText);
                command.CommandType = CommandType.StoredProcedure;
                command.Parameters.AddWithValue("@title", gameTitle);

                /* Check which stored procedure has to be executed depending on the given values */
                if (!gameSize.Equals(string.Empty) &&
                    !gamePlatform.Equals(string.Empty))
                {
                    command.CommandText = "spGetAdvancedSearchResults";
                    command.Parameters.AddWithValue("@platform", gamePlatform);
                    command.Parameters.AddWithValue("@size", gameSize);
                }
                else if (!gameSize.Equals(string.Empty))
                {
                    command.CommandText = "spGetAdvancedSearchResultsGameSize";
                    command.Parameters.AddWithValue("@size", gameSize);
                }
                else if (!gamePlatform.Equals(string.Empty))
                {
                    command.CommandText = "spGetAdvancedSearchResultsGamePlatform";
                    command.Parameters.AddWithValue("@platform", gamePlatform);
                }

                dr = Connection.GetSqlDataReader(command);
                while (dr.Read())
                {
                    Game game = new Game
                    {
                        GameNr = Cast.CastToInteger(dr["GAME_NR"]),
                        DescriptionShort = Cast.CastToString(dr["BESCHRIJVING_KORT"]),
                        Price = Cast.CastToDecimal(dr["PRIJS"]),
                        Title = Cast.CastToString(dr["TITEL"]),
                        Stock = Cast.CastToInteger(dr["VOORRAAD"]),
                        Platform = (Platform)Cast.CastToInteger(dr["PLATFORM_ID"])
                    };
                    games.Add(game);
                }
            }
            catch (Exception ex)
            {
                Log.CreateLog("Exception while getting advanced Game search results", "DATABASESTORE");
                Log.CreateLog(ex.Message, "DATABASESTORE");
            }
            finally
            {
                Connection.CloseCommand(command, dr);
            }

            return games;
        }

        /*
         * Function to check if a user has allready made a review of a specified Game.
         * 
         * @param
         * userId: The userId of the user that wants to write a review.
         * gameNr: The gameNr of the game which the user wants to write a review of.
         * 
         * @returns
         * If a review allready exists from the specified user, the function will return 1.
         * If no match was found, the function will return 0.
         * If an Exception occurs, the function will return -1.
         */
        public static int CheckGameReview(int userId, int gameNr)
        {
            SqlCommand command = null;
            string commandText = "spCheckGameReview";
            int ret = 0;

            try
            {
                command = Connection.GetCommand(commandText);
                command.CommandType = CommandType.StoredProcedure;
                command.Parameters.AddWithValue("@userId", userId);
                command.Parameters.AddWithValue("@gameNr", gameNr);
                ret = Convert.ToInt32(command.ExecuteScalar());
            }
            catch (Exception ex)
            {
                Log.CreateLog("Exception while retrieving review information for user", "DATABASESTORE");
                Log.CreateLog(ex.Message, "DATABASESTORE");
                ret = -1;
            }
            finally
            {
                Connection.CloseCommand(command);
            }
            return ret;
        }
    }
}
